/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.absisgroup.nsc.utilities;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author Hue
 */
public class PermissionControl {

    public static boolean hasPermission(final Connection con, final long userPK, final String modPK, final String secfCode) throws Exception {
        return hasPermission(con, userPK, modPK, secfCode, null);
    }

    public static boolean hasPermission(final Connection con, final long userPK, final String modPK, final String secfCode, final String secgPK) throws Exception {
        long lReturn = getSecgfAccessType(con, userPK, modPK, secfCode, secgPK);

        return (lReturn == 1);
    }

    private static long getSecgfAccessType(final Connection con, final long userPK, final String modPK, final String secfCode, final String secgPK) throws Exception {
        boolean securityLog = true;
        //check role of user
        String strQuery = "SELECT secgf_access_type, SECF_VP_FK FROM security_group_function, security_group, security_function WHERE secgf_secf_fk = secf_pk AND secgf_secg_fk = secg_pk AND secf_code = ? and SECG_MOD_FK = ? ";
        if(secgPK != null) {
            if(secgPK.toUpperCase().indexOf("NULL") != -1) {
                strQuery += "and ( secg_pk in (" + secgPK + ") " +
                            "      or secg_pk in (SELECT SECUG_SECG_FK FROM security_user_group WHERE SECUG_USR_FK = " + userPK + " ) " +
                            ")";
            }
            else {
                strQuery += "and secg_pk in (" + secgPK + ")";
            }
        }
        else {
            strQuery +=" and secg_pk in (SELECT SECUG_SECG_FK FROM security_user_group WHERE SECUG_USR_FK = " + userPK + ")";
        }
        final PreparedStatement pStmt = prepareStatement(con, strQuery, securityLog);

        pStmt.setString(1, secfCode);
        pStmt.setString(2, modPK);
        final ResultSet rs = pStmt.executeQuery();
        long lAccessType = 0;
        BigDecimal vpPK = null;
        while (rs.next() && lAccessType != 1) {
            lAccessType = rs.getLong(1);
            if(vpPK == null) {
                vpPK = rs.getBigDecimal("SECF_VP_FK");
            }
        }
        pStmt.close();

        if(vpPK != null && lAccessType == 1) {
            return callValidationProcedure(con, vpPK, securityLog);
        }
        
        return lAccessType;
    }

    private static String correctSchemaName(String schema) {
        if(schema == null || "".equals(schema)) {
            schema = "";
        }
        else if(!schema.endsWith(".")) {
            schema += ".";
        }
        return schema;
    }
    
    private static int callValidationProcedure(final Connection con, final BigDecimal vpPK, final boolean securityLog) throws java.sql.SQLException {
        final String strQuery = "select vp_schema, vp_function_name " +
                                "from validation_procedure where vp_pk = ?";
        final PreparedStatement pStmt = prepareStatement(con, strQuery, securityLog);
        pStmt.setBigDecimal(1, vpPK);
        final ResultSet rs = pStmt.executeQuery();
        String schema = null, functionName = null;
        if(rs.next()) {
            schema = rs.getString("vp_schema");
            functionName = rs.getString("vp_function_name");
        }
        pStmt.close();

        final String sql = "{ ? = call " + correctSchemaName(schema) + functionName + "() }";
        final java.sql.CallableStatement stmt = con.prepareCall(sql);
        stmt.registerOutParameter(1, java.sql.Types.NUMERIC);
        stmt.executeUpdate();
        final int iReturn = stmt.getInt(1);
        stmt.close();

        return iReturn;
    }
    
    private static PreparedStatement prepareStatement(final Connection con, final String strQuery, final boolean securityLog) throws SQLException {
        final PreparedStatement pStmt = con.prepareStatement(strQuery);
        ((DBPreparedStatement)pStmt).forceKeepParams(securityLog);

        return pStmt;
    }
}
